﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {        

        #region 产品供求
        /// <summary>
        /// 发布供应
        /// </summary>
        /// <returns></returns>
        public int InsertSupply(SupplyInfo supplyInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,supplyInfo.UserUid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProId),
                                      DbHelper.MakeInParam("@ProClsId",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(supplyInfo.ProClsId,2)),
                                      DbHelper.MakeInParam("@Numbers",(DbType)SqlDbType.NChar,15,supplyInfo.Numbers),
                                      DbHelper.MakeInParam("@proname",(DbType)SqlDbType.NChar,20,supplyInfo.Title),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,supplyInfo.Imgs),
                                      DbHelper.MakeInParam("@Price",(DbType)SqlDbType.NChar,15,supplyInfo.Price),
                                      DbHelper.MakeInParam("@ProClsName",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProClsName),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,supplyInfo.Key),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,supplyInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,supplyInfo.Area),
                                      DbHelper.MakeInParam("@DeliveryTime",(DbType)SqlDbType.NChar,12,supplyInfo.DeliveryTime),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,4,supplyInfo.Audit),
                                      DbHelper.MakeInParam("@explication",(DbType)SqlDbType.NVarChar,supplyInfo.Explication.Length,supplyInfo.Explication),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,supplyInfo.Maturity.Length,supplyInfo.Maturity),
                                      DbHelper.MakeInParam("@minorder",(DbType)SqlDbType.NVarChar,supplyInfo.MinOrder.Length,supplyInfo.MinOrder),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,supplyInfo.Date.Length,supplyInfo.Date),
                                      DbHelper.MakeInParam("@suttle",(DbType)SqlDbType.NChar,15,supplyInfo.Suttle),
                                      DbHelper.MakeInParam("@unit",(DbType)SqlDbType.NChar,12,supplyInfo.Unit),
                                      DbHelper.MakeInParam("@moneyunit",(DbType)SqlDbType.NChar,12,supplyInfo.MoneyUnit)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}supply] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([proid],[userid],[proname],[proclsid],[proclsname] ,[key],[description],[explication],[imgs],[minorder],[numbers],[suttle],[moneyunit],[unit],[area],[price],[audit],[deliverytime],[maturity],[date])");
            commantText.Append("VALUES(@proid,@userid,@proname,@proclsid,@proclsname,@key,@description,@explication,@imgs,@minorder,@numbers,@suttle,@moneyunit,@unit,@area,@price,@audit,@deliverytime,@maturity,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和产品ID更新供应信息
        /// </summary>
        /// <param name="supplyInfo"></param>
        /// <returns></returns>
        public int UpdateSupplyByProIdAndUserID(SupplyInfo supplyInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,supplyInfo.UserUid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProId),
                                      DbHelper.MakeInParam("@ProClsId",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(supplyInfo.ProClsId,2)),
                                      DbHelper.MakeInParam("@Numbers",(DbType)SqlDbType.NChar,15,supplyInfo.Numbers),
                                      DbHelper.MakeInParam("@proname",(DbType)SqlDbType.NChar,20,supplyInfo.Title),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,supplyInfo.Imgs),
                                      DbHelper.MakeInParam("@Price",(DbType)SqlDbType.NChar,15,supplyInfo.Price),
                                      DbHelper.MakeInParam("@ProClsName",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProClsName),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,supplyInfo.Key),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,supplyInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,supplyInfo.Area),
                                      DbHelper.MakeInParam("@DeliveryTime",(DbType)SqlDbType.NChar,12,supplyInfo.DeliveryTime),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,4,supplyInfo.Audit),
                                      DbHelper.MakeInParam("@explication",(DbType)SqlDbType.NVarChar,supplyInfo.Explication.Length,supplyInfo.Explication),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,supplyInfo.Maturity.Length,supplyInfo.Maturity),
                                      DbHelper.MakeInParam("@minorder",(DbType)SqlDbType.NVarChar,supplyInfo.MinOrder.Length,supplyInfo.MinOrder),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,supplyInfo.Date.Length,supplyInfo.Date),
                                      DbHelper.MakeInParam("@suttle",(DbType)SqlDbType.NChar,15,supplyInfo.Suttle),
                                      DbHelper.MakeInParam("@unit",(DbType)SqlDbType.NChar,12,supplyInfo.Unit),
                                      DbHelper.MakeInParam("@moneyunit",(DbType)SqlDbType.NChar,12,supplyInfo.MoneyUnit)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}supply] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[proname]=@proname,[proclsid]=@proclsid,[proclsname]=@proclsname ,[key]=@key,[description]=@description,");
            commantText.Append("[explication]=@explication,[imgs]=@imgs,[minorder]=@minorder,[numbers]=@numbers,[suttle]=@suttle,[moneyunit]=@moneyunit,");
            commantText.Append("[unit]=@unit,[area]=@area,[price]=@price,[audit]=@audit,[deliverytime]=@deliverytime,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [proid]=@proid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        
        /// <summary>
        /// 根据proid更新供应信息
        /// </summary>
        /// <param name="supplyInfo"></param>
        /// <returns></returns>
        public int UpdateSupplyByProId(SupplyInfo supplyInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProId),
                                      DbHelper.MakeInParam("@ProClsId",(DbType)SqlDbType.Int,int.MaxValue,Utils.StrToInt(supplyInfo.ProClsId,2)),
                                      DbHelper.MakeInParam("@Numbers",(DbType)SqlDbType.NChar,15,supplyInfo.Numbers),
                                      DbHelper.MakeInParam("@proname",(DbType)SqlDbType.NChar,20,supplyInfo.Title),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,supplyInfo.Imgs),
                                      DbHelper.MakeInParam("@Price",(DbType)SqlDbType.NChar,15,supplyInfo.Price),
                                      DbHelper.MakeInParam("@ProClsName",(DbType)SqlDbType.NVarChar,60,supplyInfo.ProClsName),
                                      DbHelper.MakeInParam("@Key",(DbType)SqlDbType.NVarChar,100,supplyInfo.Key),
                                      DbHelper.MakeInParam("@Description",(DbType)SqlDbType.NVarChar,200,supplyInfo.Description),                                     
                                      DbHelper.MakeInParam("@Area",(DbType)SqlDbType.NChar,40,supplyInfo.Area),
                                      DbHelper.MakeInParam("@DeliveryTime",(DbType)SqlDbType.NChar,12,supplyInfo.DeliveryTime),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,4,supplyInfo.Audit),
                                      DbHelper.MakeInParam("@explication",(DbType)SqlDbType.NVarChar,supplyInfo.Explication.Length,supplyInfo.Explication),
                                      DbHelper.MakeInParam("@Maturity",(DbType)SqlDbType.NChar,supplyInfo.Maturity.Length,supplyInfo.Maturity),
                                      DbHelper.MakeInParam("@minorder",(DbType)SqlDbType.NVarChar,supplyInfo.MinOrder.Length,supplyInfo.MinOrder),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,supplyInfo.Date.Length,supplyInfo.Date),
                                      DbHelper.MakeInParam("@suttle",(DbType)SqlDbType.NChar,15,supplyInfo.Suttle),
                                      DbHelper.MakeInParam("@unit",(DbType)SqlDbType.NChar,12,supplyInfo.Unit),
                                      DbHelper.MakeInParam("@moneyunit",(DbType)SqlDbType.NChar,12,supplyInfo.MoneyUnit)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}supply] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[proname]=@proname,[proclsid]=@proclsid,[proclsname]=@proclsname ,[key]=@key,[description]=@description,");
            commantText.Append("[explication]=@explication,[imgs]=@imgs,[minorder]=@minorder,[numbers]=@numbers,[suttle]=@suttle,[moneyunit]=@moneyunit,");
            commantText.Append("[unit]=@unit,[area]=@area,[price]=@price,[audit]=@audit,[deliverytime]=@deliverytime,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [proid]=@proid ");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据USERID和PROID更新IMGS内容
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <param name="imgs"></param>
        /// <returns></returns>
        public int UpdateSupplyImgs(string userid, string proid, string imgs)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@UserId",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@Proid",(DbType)SqlDbType.NVarChar,60,proid),
                                      DbHelper.MakeInParam("@Imgs",(DbType)SqlDbType.NVarChar,120,imgs)
                                  };
            string commantText = string.Format("UPDATE [ZhangBY].[dbo].[{0}supply] SET [imgs]=@imgs WHERE [userid]=@userid AND [proid]=@proid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commantText,parms);
        }
        /// <summary>
        /// 根据USERID和PROID查找供应信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetSupplyByProIdAndUserId(string userid,string proid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}supply] WHERE [userid]=@userid AND [proid]=@proid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据PROID查找供应信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetSupplyByProId(string proid)
        {
            DbParameter parms = DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}supply] WHERE [proid]=@proid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有供应数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetSupplyByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}supply] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和PROID删除供应信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelSupplyByUserIdAndProId(string userid, string proid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}supply] WHERE [userid]=@userid AND [proid]=@proid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据PROID删除供应信息
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public int DelSupplyByProId(string proid)
        {
            DbParameter parms = DbHelper.MakeInParam("@proid",(DbType)SqlDbType.NVarChar,60,proid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}supply] WHERE [proid]=@proid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得供应产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountSupplyByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(proid) FROM [dbo].[{0}supply] WHERE [userid]=@userid",BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        #endregion
        
    }
}